/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.eschool.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.util.Collections;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;

import com.eschool.model.RechargeHistory;
import com.eschool.model.SaasAdmin;
import com.eschool.model.School;
import com.eschool.util.DateFormaterUtil;
import com.eschool.util.HibernateUtil;
import com.eschool.util.JdbcUtil;

/**
 * 
 * @author Ammar
 */

public class SchoolDao {

	public static boolean addSchool(School school) {
		boolean isInserted = false;

		Connection connection = JdbcUtil.openConnection();
		try {
			CallableStatement storedProc = connection
					.prepareCall("{ call InsertSchool(?,?,?,?,?,?,?,?,?,?,?,?) }");
			storedProc.setString(1, school.getSchoolName());
			storedProc.setString(2, school.getShortName());
			storedProc.setString(3, school.getHeadName());
			storedProc.setString(4, school.getWebsite());
			storedProc.setString(5, school.getAddress());
			storedProc.setString(6, school.getDescription());
			storedProc.setString(7, school.getPhoneNumber());
			storedProc.setString(8, school.getFaxNumber());
			storedProc.setString(
					9,
					DateFormaterUtil.format(DateFormaterUtil.SQL_DATE,
							school.getCreationDate()));
			storedProc.setString(
					10,
					DateFormaterUtil.format(DateFormaterUtil.SQL_DATE,
							school.getExpiryDate()));
			storedProc.setBoolean(11, school.isAccountStatus());
			storedProc.setInt(12, school.getCreator().getAdminId());
			storedProc.execute();

			isInserted = true;
			connection.close();
		}

		catch (Exception e) {
			isInserted = false;
			connection.close();
			e.printStackTrace();
		} finally {
			return isInserted;
		}

	}

	public static School getSchoolByProperty(String property, Object value) {

		Session session = HibernateUtil.currentSession();

		Criteria crit = session.createCriteria(School.class);
		crit.add(Restrictions.eq(property, value));

		School school = (School) crit.uniqueResult();

		HibernateUtil.closeSession(session);

		return school;
	}

	public static List getSchools(Map<String, Object> conditions) {

		Session session = HibernateUtil.currentSession();
		Criteria crit = session.createCriteria(School.class);

		if (conditions != null)

			for (Iterator iterator = conditions.entrySet().iterator(); iterator
					.hasNext();) {

				Map.Entry condition = (Map.Entry) iterator.next();

				crit.add(Restrictions.eq(((String) condition.getKey()),
						condition.getValue()));
			}

		List schools = (List<School>) crit.list();

		HibernateUtil.closeSession(session);

		if (schools == null)
			return Collections.EMPTY_LIST;

		return schools;
	}
	
	public static List getSchoolsLike(String name) {

		Session session = HibernateUtil.currentSession();
		Criteria crit = session.createCriteria(School.class);
		
		crit.add(Restrictions.like("schoolName", name+"%"));
		
		List schools = (List<School>) crit.list();

		HibernateUtil.closeSession(session);

		if (schools == null)
			return Collections.EMPTY_LIST;

		return schools;
	}
	
	public static boolean recharge(School school) {
		boolean isUpdated = false;

		Connection connection = JdbcUtil.openConnection();
		try {
			CallableStatement storedProc = connection
					.prepareCall("{ call RechargeSchool(?,?) }");
			storedProc.setInt(1, school.getSchoolId());
			storedProc.setString(2,
					DateFormaterUtil.format(DateFormaterUtil.SQL_DATE, school.getExpiryDate()));
			storedProc.execute();

			isUpdated = true;
			connection.close();
		}

		catch (Exception e) {
			isUpdated= false;
			connection.close();
			e.printStackTrace();
		} finally {
			return isUpdated;
		}
	}
	
	public static boolean insertHistory(School school, SaasAdmin admin, Date date) {
		boolean isUpdated = false;

		Connection connection = JdbcUtil.openConnection();
		try {
			CallableStatement storedProc = connection
					.prepareCall("{ call spHistoryInsert(?,?,?) }");
			storedProc.setInt(2, school.getSchoolId());
			storedProc.setInt(1, admin.getAdminId());
			storedProc.setString(3,
					DateFormaterUtil.format(DateFormaterUtil.SQL_DATE, date));
			storedProc.execute();

			isUpdated = true;
			connection.close();
		}

		catch (Exception e) {
			isUpdated= false;
			connection.close();
			e.printStackTrace();
		} finally {
			return isUpdated;
		}
	}
	
	public static boolean updateStatus(School school) {
		boolean isUpdated = false;

		Connection connection = JdbcUtil.openConnection();
		try {
			CallableStatement storedProc = connection
					.prepareCall("{ call UpdateSchoolStatus(?,?) }");
			storedProc.setInt(1, school.getSchoolId());
			storedProc.setBoolean(2, school.isAccountStatus());
			storedProc.execute();

			isUpdated = true;
			connection.close();
		}

		catch (Exception e) {
			isUpdated= false;
			connection.close();
			e.printStackTrace();
		} finally {
			return isUpdated;
		}
	}
	
	public static List getRechargeHistory() {

		Session session = HibernateUtil.currentSession();
		Criteria crit = session.createCriteria(RechargeHistory.class);
		crit.addOrder(Order.asc("school"));
		
		List rechargeHistory = (List<RechargeHistory>) crit.list();

		HibernateUtil.closeSession(session);

		if (rechargeHistory == null)
			return Collections.EMPTY_LIST;

		return rechargeHistory;
	}
	
	public static List getSchoolRechargeHistory(School school) {

		Session session = HibernateUtil.currentSession();
		Criteria crit = session.createCriteria(RechargeHistory.class);
		
		crit.add(Restrictions.eq("school", school));
		
		List rechargeHistory = (List<RechargeHistory>) crit.list();

		HibernateUtil.closeSession(session);

		if (rechargeHistory == null)
			return Collections.EMPTY_LIST;

		return rechargeHistory;
	}
	
	
	
	
	
}
